package com.spark.gaea.data.utils;

import java.util.Map;

public class SQLUtil {
	public static String insertWithNullToBlank(final String tableName, final Map<String, Object> paraMap) {
		final StringBuffer fields = new StringBuffer();
		final StringBuffer vars = new StringBuffer();
		for (final Map.Entry entry : paraMap.entrySet()) {
			fields.append(entry.getKey()).append(",");
			vars.append("'").append((entry.getValue() == null) ? "" : entry.getValue().toString()).append("',");
		}
		if (fields.toString().endsWith(",")) {
			fields.setLength(fields.length() - 1);
		}
		if (vars.toString().endsWith(",")) {
			vars.setLength(vars.length() - 1);
		}
		final StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO ").append(tableName).append("(").append(fields.toString()).append(")");
		sql.append(" VALUES ").append("(").append(vars.toString()).append(")");
		return sql.toString();
	}

	public static String insertWithNullToNull(final String tableName, final Map<String, Object> paraMap) {
		final StringBuffer fields = new StringBuffer();
		final StringBuffer vars = new StringBuffer();
		for (final Map.Entry entry : paraMap.entrySet()) {
			fields.append(entry.getKey()).append(",");
			if (entry.getValue() == null) {
				vars.append("null,");
			} else {
				vars.append("'").append((entry.getValue() == null) ? "" : entry.getValue().toString()).append("',");
			}
		}
		if (fields.toString().endsWith(",")) {
			fields.setLength(fields.length() - 1);
		}
		if (vars.toString().endsWith(",")) {
			vars.setLength(vars.length() - 1);
		}
		final StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO ").append(tableName).append("(").append(fields.toString()).append(")");
		sql.append(" VALUES ").append("(").append(vars.toString()).append(")");
		return sql.toString();
	}

	public static String updateWithNullToBlank(final String tableName, final Map<String, Object> paraMap) {
		final StringBuffer fieldValue = new StringBuffer();
		for (final Map.Entry entry : paraMap.entrySet()) {
			if (!"ID".equals(entry.getKey().toString())) {
				fieldValue.append(entry.getKey()).append("=");
				fieldValue.append("'").append((entry.getValue() == null) ? "" : entry.getValue().toString())
						.append("',");
			}
		}
		if (fieldValue.toString().endsWith(",")) {
			fieldValue.setLength(fieldValue.length() - 1);
		}
		final StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ").append(tableName).append(" SET ").append(fieldValue.toString()).append(" ");
		sql.append(" WHERE ").append("ID=").append("'" + paraMap.get("ID").toString() + "'");
		return sql.toString();
	}

	public static String updateWithNullToNull(final String tableName, final Map<String, Object> paraMap) {
		final StringBuffer fieldValue = new StringBuffer();
		for (final Map.Entry entry : paraMap.entrySet()) {
			if (!"ID".equals(entry.getKey().toString())) {
				fieldValue.append(entry.getKey()).append("=");
				if (entry.getValue() == null) {
					fieldValue.append("null,");
				} else {
					fieldValue.append("'").append((entry.getValue() == null) ? "" : entry.getValue().toString())
							.append("',");
				}
			}
		}
		if (fieldValue.toString().endsWith(",")) {
			fieldValue.setLength(fieldValue.length() - 1);
		}
		final StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ").append(tableName).append(" SET ").append(fieldValue.toString()).append(" ");
		sql.append(" WHERE ").append("ID=").append("'" + paraMap.get("ID").toString() + "'");
		return sql.toString();
	}

	public static String getUpdateStatementByCode(final String tableName, final Map<String, Object> paraMap) {
		final StringBuffer fieldValue = new StringBuffer();
		for (final Map.Entry entry : paraMap.entrySet()) {
			if (!"CODE".equals(entry.getKey().toString())) {
				fieldValue.append(entry.getKey()).append("=");
				fieldValue.append("'").append((entry.getValue() == null) ? "" : entry.getValue().toString())
						.append("',");
			}
		}
		if (fieldValue.toString().endsWith(",")) {
			fieldValue.setLength(fieldValue.length() - 1);
		}
		final StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ").append(tableName).append(" SET ").append(fieldValue.toString()).append(" ");
		sql.append(" WHERE ").append("CODE=").append("'" + paraMap.get("CODE").toString() + "'");
		return sql.toString();
	}

	public static String getUpdateStatementByKey(final String tableName, final String keyName,
			final Map<String, Object> paraMap) {
		final StringBuffer fieldValue = new StringBuffer();
		for (final Map.Entry entry : paraMap.entrySet()) {
			if (!keyName.equals(entry.getKey().toString())) {
				fieldValue.append(entry.getKey()).append("=");
				fieldValue.append("'").append((entry.getValue() == null) ? "" : entry.getValue().toString())
						.append("',");
			}
		}
		if (fieldValue.toString().endsWith(",")) {
			fieldValue.setLength(fieldValue.length() - 1);
		}
		final StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ").append(tableName).append(" SET ").append(fieldValue.toString()).append(" ");
		sql.append(" WHERE ").append(keyName).append("=").append("'" + paraMap.get(keyName).toString() + "'");
		return sql.toString();
	}

	public static String getUpdateStatusStatement(final String tableName, final String keyName, final Object keyValue,
			final String updateName, final Object updateValue) {
		final StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ").append(tableName);
		sql.append(" SET ").append(updateName).append("=").append("'").append(updateValue.toString()).append("'");
		sql.append(" WHERE ").append(keyName).append("=").append("'" + keyValue.toString() + "'");
		return sql.toString();
	}

	public static String getDeleteStatementByKey(final String tableName, final String keyName, final Object keyValue) {
		final StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM  ").append(tableName);
		sql.append(" WHERE ").append(keyName).append("=").append("'" + keyValue.toString() + "'");
		return sql.toString();
	}
}
